package com.accounting.db;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.HashMap;
import java.util.Arrays;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.LocalDateTime;
import java.util.stream.Collectors;
import com.accounting.model.User;
import com.accounting.model.BankCard;
import com.accounting.model.AssetType;
import com.accounting.model.Asset;
import com.accounting.model.AssetHistory;
import com.accounting.model.AssetIncome;
import com.accounting.model.AssetIncomeDetail;
import com.accounting.model.Transaction;
import javafx.collections.ObservableList;
import com.accounting.model.AssetSnapshot;
import com.accounting.model.AssetSnapshotDetail;
import com.accounting.model.CategoryTotal;

public class DatabaseManager {
    private static final String DB_URL = "jdbc:sqlite:accounting.db";
    private static final Map<String, String> userRoleCache = new HashMap<>();

    public static void initDatabase() {
        try (Connection conn = getConnection()) {
            Statement stmt = conn.createStatement();
            
            // 1. 创建基础表（如果不存在）
            // 创建 assets 表
            String createAssetsTableSQL = """
                CREATE TABLE IF NOT EXISTS assets (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    name TEXT NOT NULL,
                    type_name TEXT NOT NULL,
                    category TEXT NOT NULL,
                    account_number TEXT,
                    balance REAL NOT NULL DEFAULT 0,
                    remark TEXT,
                    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY(user_id) REFERENCES users(id)
                )
            """;
            stmt.execute(createAssetsTableSQL);
            
            // 创建 transactions 表
            String createTransTableSQL = """
                CREATE TABLE IF NOT EXISTS transactions (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    date TEXT NOT NULL,
                    type TEXT NOT NULL,
                    category TEXT NOT NULL,
                    amount REAL NOT NULL,
                    description TEXT,
                    user_id INTEGER,
                    asset_id INTEGER,
                    FOREIGN KEY(user_id) REFERENCES users(id),
                    FOREIGN KEY(asset_id) REFERENCES assets(id)
                )
            """;
            stmt.execute(createTransTableSQL);
            
            // 创建 categories 表
            String createCategoryTableSQL = """
                CREATE TABLE IF NOT EXISTS categories (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    type TEXT NOT NULL,
                    name TEXT NOT NULL,
                    sort_order INTEGER DEFAULT 0,
                    UNIQUE(type, name)
                )
            """;
            stmt.execute(createCategoryTableSQL);
            
            // 创建 banks 表
            String createBanksTableSQL = """
                CREATE TABLE IF NOT EXISTS banks (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL UNIQUE,
                    logo_path TEXT
                )
            """;
            stmt.execute(createBanksTableSQL);
            
            // 创建快照相关的表
            createSnapshotTables(conn);
            
            // 2. 检查并创建管理员账户（如果不存在）
            String checkAdminSQL = "SELECT COUNT(*) FROM users WHERE username = 'admin'";
            ResultSet rs = stmt.executeQuery(checkAdminSQL);
            if (rs.next() && rs.getInt(1) == 0) {
                String createAdminSQL = """
                    INSERT INTO users (username, password, name, role) 
                    VALUES ('admin', 'admin', '管理员', 'admin')
                """;
                stmt.execute(createAdminSQL);
            }
            
            // 初始化预设银行
            String checkBanksSQL = "SELECT COUNT(*) FROM banks";
            ResultSet rsBanks = stmt.executeQuery(checkBanksSQL);
            if (rsBanks.next() && rsBanks.getInt(1) == 0) {
                // 预设银行列表
                String[] defaultBanks = {
                    "工商银行", "建设银行", "农业银行", "中国银行", 
                    "交通银行", "招商银行", "浦发银行", "中信银行",
                    "光大银行", "民生银行", "华夏银行", "广发银行",
                    "平安银行", "兴业银行", "邮储银行"
                };
                
                String insertBankSQL = "INSERT INTO banks (name) VALUES (?)";
                PreparedStatement pstmt = conn.prepareStatement(insertBankSQL);
                
                for (String bank : defaultBanks) {
                    pstmt.setString(1, bank);
                    pstmt.executeUpdate();
                }
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("初始化数据库失败: " + e.getMessage());
        }
    }

    private static void initAssetTypes(Connection conn) {
        try {
            // 检查是否已经初始化
            String checkSQL = "SELECT COUNT(*) FROM asset_types";
            ResultSet rs = conn.createStatement().executeQuery(checkSQL);
            if (rs.next() && rs.getInt(1) > 0) {
                return;
            }

            // 预设资产类型
            String[][] assetTypes = {
                // {名称, 分类}
                {"储蓄卡", "银行账户"},
                {"信用卡", "银行账户"},
                {"余额宝", "网络支付"},
                {"微信钱包", "网络支付"},
                {"支付宝", "网络支付"},
                {"花呗", "信用账户"},
                {"京东白条", "信用账户"},
                {"现金", "现金"},
                {"理财产品", "投资理财"},
                {"基金", "投资理财"},
                {"股票", "投资理财"},
                {"定期存款", "定期理财"},
                {"公积金", "社保公积金"},
                {"养老保险", "社保公积金"},
                {"医疗保险", "社保公积金"}
            };

            String sql = "INSERT INTO asset_types (name, category, sort_order) VALUES (?, ?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            
            int order = 0;
            for (String[] type : assetTypes) {
                pstmt.setString(1, type[0]);
                pstmt.setString(2, type[1]);
                pstmt.setInt(3, order++);
                pstmt.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void initDefaultCategories(Connection conn) throws SQLException {
        // 检查是否已经有类别数据
        String checkSQL = "SELECT COUNT(*) FROM categories";
        ResultSet rs = conn.createStatement().executeQuery(checkSQL);
        if (rs.next() && rs.getInt(1) > 0) {
            return; // 已有数据，不需要初始化
        }

        // 初始化收入类别
        String[] incomeCategories = {
            "工资", "奖金", "投资收益", "兼职收入", "其他收入"
        };

        // 初始化支出类别
        String[] expenseCategories = {
            "餐饮", "交通", "购物", "居住", "娱乐", "医疗", 
            "教育", "旅游", "通讯", "其他支出"
        };

        String insertSQL = "INSERT INTO categories (type, name, sort_order) VALUES (?, ?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(insertSQL);

        // 插入收入类别
        int order = 0;
        for (String category : incomeCategories) {
            pstmt.setString(1, "收入");
            pstmt.setString(2, category);
            pstmt.setInt(3, order++);
            pstmt.executeUpdate();
        }

        // 插入支出类别
        order = 0;
        for (String category : expenseCategories) {
            pstmt.setString(1, "支出");
            pstmt.setString(2, category);
            pstmt.setInt(3, order++);
            pstmt.executeUpdate();
        }
    }

    private static void initDefaultBanks(Connection conn) {
        try {
            // 检查是否已经初始化
            String checkSQL = "SELECT COUNT(*) FROM banks";
            ResultSet rs = conn.createStatement().executeQuery(checkSQL);
            if (rs.next() && rs.getInt(1) > 0) {
                return;
            }

            // 预置银行列表
            String[] banks = {
                "中国工商银行",
                "中国农业银行",
                "中国建设银行",
                "中国银行",
                "交通银行",
                "中国邮政储蓄银行",
                "招商银行",
                "中信银行",
                "浦发银行",
                "民生银行",
                "华夏银行",
                "广发银行",
                "平安银行",
                "兴业银行"
            };

            String sql = "INSERT INTO banks (name) VALUES (?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            
            for (String bank : banks) {
                pstmt.setString(1, bank);
                pstmt.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 获取指定类型的所有类别
    public static List<String> getCategories(String type) {
        List<String> categories = new ArrayList<>();
        String sql = "SELECT name FROM categories WHERE type = ? ORDER BY sort_order";
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, type);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                categories.add(rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return categories;
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL);
    }

    public static void initializeCategories() {
        try (Connection conn = getConnection()) {
            // 检查是否已经有类别数据
            String checkSql = "SELECT COUNT(*) FROM categories";
            ResultSet rs = conn.createStatement().executeQuery(checkSql);
            if (rs.next() && rs.getInt(1) == 0) {
                // 添加预设的支出类别
                String[] expenseCategories = {
                    "餐饮", "交通", "购物", "居住", "娱乐",
                    "医疗", "教育", "通讯", "服装", "日用品",
                    "水电煤", "保险", "育儿", "宠物", "其他支出"
                };
                
                // 添加预设的收入类别
                String[] incomeCategories = {
                    "工资", "奖金", "投资收益", "兼职", "租金收入",
                    "利息收入", "报销", "红包", "其他收入"
                };
                
                String sql = "INSERT INTO categories (type, name) VALUES (?, ?)";
                PreparedStatement pstmt = conn.prepareStatement(sql);
                
                // 插入支出类别
                for (String category : expenseCategories) {
                    pstmt.setString(1, "支出");
                    pstmt.setString(2, category);
                    pstmt.executeUpdate();
                }
                
                // 插入收入类别
                for (String category : incomeCategories) {
                    pstmt.setString(1, "收入");
                    pstmt.setString(2, category);
                    pstmt.executeUpdate();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static boolean addCategory(String type, String name) {
        try (Connection conn = getConnection()) {
            String sql = "INSERT INTO categories (type, name) VALUES (?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, type);
            pstmt.setString(2, name);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static boolean updateCategory(String type, String oldName, String newName) {
        try (Connection conn = getConnection()) {
            String sql = "UPDATE categories SET name = ? WHERE type = ? AND name = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, newName);
            pstmt.setString(2, type);
            pstmt.setString(3, oldName);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static boolean deleteCategory(String type, String name) {
        try (Connection conn = getConnection()) {
            String sql = "DELETE FROM categories WHERE type = ? AND name = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, type);
            pstmt.setString(2, name);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 用户相关方法
    public static boolean validateUser(String username, String password) {
        try (Connection conn = getConnection()) {
            String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            ResultSet rs = pstmt.executeQuery();
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static String getUserRole(String username) {
        if (userRoleCache.containsKey(username)) {
            return userRoleCache.get(username);
        }
        
        try (Connection conn = getConnection()) {
            String sql = "SELECT role FROM users WHERE username = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                String role = rs.getString("role");
                userRoleCache.put(username, role);
                return role;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "member";
    }

    public static boolean isUsernameExists(String username) {
        try (Connection conn = getConnection()) {
            String sql = "SELECT COUNT(*) FROM users WHERE username = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            return rs.next() && rs.getInt(1) > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static boolean registerUser(String username, String password, String name, String avatarPath) {
        if (isUsernameExists(username)) {
            return false;
        }
        
        try (Connection conn = getConnection()) {
            conn.setAutoCommit(false);
            try {
                String sql = "INSERT INTO users (username, password, name, avatar_path) VALUES (?, ?, ?, ?)";
                PreparedStatement pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, username);
                pstmt.setString(2, password);
                pstmt.setString(3, name);
                pstmt.setString(4, avatarPath);
                
                boolean success = pstmt.executeUpdate() > 0;
                if (success) {
                    conn.commit();
                    return true;
                } else {
                    conn.rollback();
                    return false;
                }
            } catch (SQLException e) {
                conn.rollback();
                throw e;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static List<User> getAllUsers() {
        List<User> users = new ArrayList<>();
        try (Connection conn = getConnection()) {
            String sql = "SELECT * FROM users ORDER BY create_time DESC";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setName(rs.getString("name"));
                user.setAvatarPath(rs.getString("avatar_path"));
                user.setRole(rs.getString("role"));
                user.setCreateTime(rs.getString("create_time"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }

    public static boolean deleteUser(int userId) {
        try (Connection conn = getConnection()) {
            String sql = "DELETE FROM users WHERE id = ? AND username != 'admin'";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static boolean updateUser(User user) {
        try (Connection conn = getConnection()) {
            String sql = "UPDATE users SET name = ?, avatar_path = ? WHERE id = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getAvatarPath());
            pstmt.setInt(3, user.getId());
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static boolean updateUserRole(int userId, String role) {
        try (Connection conn = getConnection()) {
            String sql = "UPDATE users SET role = ? WHERE id = ? AND username != 'admin'";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, role);
            pstmt.setInt(2, userId);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 清除用户角色缓存
    public static void clearUserRoleCache() {
        userRoleCache.clear();
    }

    public static String getUserAvatarPath(String username) {
        try (Connection conn = getConnection()) {
            String sql = "SELECT avatar_path FROM users WHERE username = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getString("avatar_path");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static String getUserName(String username) {
        try (Connection conn = getConnection()) {
            String sql = "SELECT name FROM users WHERE username = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getString("name");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return username;
    }

    // 获取所有银行
    public static List<String> getAllBanks() {
        List<String> banks = new ArrayList<>();
        try (Connection conn = getConnection()) {
            String sql = "SELECT name FROM banks ORDER BY name";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            
            while (rs.next()) {
                banks.add(rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return banks;
    }

    // 获取所有资产类型
    public static List<AssetType> getAllAssetTypes() {
        List<AssetType> types = new ArrayList<>();
        try (Connection conn = getConnection()) {
            String sql = "SELECT * FROM asset_types ORDER BY category, sort_order";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            
            while (rs.next()) {
                AssetType type = new AssetType();
                type.setId(rs.getInt("id"));
                type.setName(rs.getString("name"));
                type.setCategory(rs.getString("category"));
                type.setIconPath(rs.getString("icon_path"));
                type.setSortOrder(rs.getInt("sort_order"));
                types.add(type);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return types;
    }

    // 获取用户的所有资产
    public static List<Asset> getUserAssets(int userId) {
        List<Asset> assets = new ArrayList<>();
        String sql = """
            SELECT * FROM assets 
            WHERE user_id = ?
            ORDER BY category, name
        """;
        
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();
            
            while (rs.next()) {
                Asset asset = new Asset();
                asset.setId(rs.getInt("id"));
                asset.setUserId(rs.getInt("user_id"));
                asset.setName(rs.getString("name"));
                asset.setTypeName(rs.getString("type_name"));
                asset.setCategory(rs.getString("category"));
                asset.setAccountNumber(rs.getString("account_number"));
                asset.setBalance(rs.getDouble("balance"));
                asset.setRemark(rs.getString("remark"));
                assets.add(asset);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return assets;
    }

    public static int getUserId(String username) {
        try (Connection conn = getConnection()) {
            String sql = "SELECT id FROM users WHERE username = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("id");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    // 添加资产
    public static boolean addAsset(Asset asset) {
        String sql = """
            INSERT INTO assets (
                user_id, name, type_name, category,
                account_number, balance, remark
            ) VALUES (?, ?, ?, ?, ?, ?, ?)
        """;
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, asset.getUserId());
            pstmt.setString(2, asset.getName());
            pstmt.setString(3, asset.getTypeName());
            pstmt.setString(4, getAssetCategory(asset.getTypeName()));
            pstmt.setString(5, asset.getAccountNumber());
            pstmt.setDouble(6, asset.getBalance());
            pstmt.setString(7, asset.getRemark());
            
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 更新资产
    public static boolean updateAsset(Asset asset) {
        String sql = """
            UPDATE assets 
            SET name = ?, 
                type_name = ?, 
                category = ?, 
                account_number = ?, 
                balance = ?, 
                remark = ?, 
                update_time = CURRENT_TIMESTAMP
            WHERE id = ?
        """;
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, asset.getName());
            pstmt.setString(2, asset.getTypeName());
            pstmt.setString(3, getAssetCategory(asset.getTypeName()));
            pstmt.setString(4, asset.getAccountNumber());
            pstmt.setDouble(5, asset.getBalance());
            pstmt.setString(6, asset.getRemark());
            pstmt.setInt(7, asset.getId());
            
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 删除资产
    public static boolean deleteAsset(int assetId) {
        try (Connection conn = getConnection()) {
            String sql = "DELETE FROM assets WHERE id = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, assetId);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 获取用户的所有银行卡
    public static List<BankCard> getUserBankCards(int userId) {
        List<BankCard> cards = new ArrayList<>();
        String sql = """
            SELECT * FROM assets 
            WHERE user_id = ? 
            AND category = '银行账户'
            ORDER BY name
        """;
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, userId);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                BankCard card = new BankCard();
                card.setId(rs.getInt("id"));
                card.setBankName(rs.getString("name"));
                card.setAccountNumber(rs.getString("account_number"));
                card.setBalance(rs.getDouble("balance"));
                cards.add(card);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return cards;
    }

    // 添加银行卡
    public static boolean addBankCard(BankCard card) {
        try (Connection conn = getConnection()) {
            // 首先获取资产类型ID
            String typeSQL = "SELECT id FROM asset_types WHERE name = ? AND category = '银行账户'";
            PreparedStatement typePstmt = conn.prepareStatement(typeSQL);
            typePstmt.setString(1, card.getCardType());
            ResultSet rs = typePstmt.executeQuery();
            if (!rs.next()) {
                return false;
            }
            int typeId = rs.getInt("id");
            
            // 添加银行卡作为资产
            String sql = """
                INSERT INTO assets (user_id, type_id, name, account_number, balance, remark)
                VALUES (?, ?, ?, ?, ?, ?)
                """;
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, card.getUserId());
            pstmt.setInt(2, typeId);
            pstmt.setString(3, card.getBankName());
            pstmt.setString(4, card.getCardNumber());
            pstmt.setDouble(5, card.getBalance());
            pstmt.setString(6, card.getRemark());
            
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 更新银行卡余额
    public static boolean updateBankCardBalance(int cardId, double balance) {
        try (Connection conn = getConnection()) {
            String sql = "UPDATE assets SET balance = ? WHERE id = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setDouble(1, balance);
            pstmt.setInt(2, cardId);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 删除银行卡
    public static boolean deleteBankCard(int cardId) {
        try (Connection conn = getConnection()) {
            String sql = "DELETE FROM assets WHERE id = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, cardId);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 添加资产历史记录
    public static boolean addAssetHistory(int assetId, String type, double amount, double balance, String remark) {
        String sql = """
            INSERT INTO asset_history (asset_id, date, type, amount, balance, remark)
            VALUES (?, date('now'), ?, ?, ?, ?)
            """;
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, assetId);
            pstmt.setString(2, type);
            pstmt.setDouble(3, amount);
            pstmt.setDouble(4, balance);
            pstmt.setString(5, remark);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 获取资产历史记录
    public static List<AssetHistory> getAssetHistory(int assetId) {
        List<AssetHistory> history = new ArrayList<>();
        String sql = """
            SELECT * FROM asset_history 
            WHERE asset_id = ? 
            ORDER BY date DESC, id DESC
            """;
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, assetId);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                AssetHistory record = new AssetHistory();
                record.setId(rs.getInt("id"));
                record.setAssetId(rs.getInt("asset_id"));
                record.setDate(LocalDate.parse(rs.getString("date")));
                record.setType(rs.getString("type"));
                record.setAmount(rs.getDouble("amount"));
                record.setBalance(rs.getDouble("balance"));
                record.setRemark(rs.getString("remark"));
                history.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return history;
    }

    // 添加资产收益记录
    public static boolean addAssetIncome(AssetIncome income) {
        String sql = """
            INSERT INTO asset_income (asset_id, date, type, amount, remark)
            VALUES (?, ?, ?, ?, ?)
            """;
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            pstmt.setInt(1, income.getAssetId());
            pstmt.setString(2, income.getDate().toString());
            pstmt.setString(3, income.getType());
            pstmt.setDouble(4, income.getAmount());
            pstmt.setString(5, income.getRemark());
            
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows == 0) {
                return false;
            }
            
            // 获取生成的收益ID
            try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    income.setId(generatedKeys.getInt(1));
                }
            }
            
            // 添加收益明细
            if (income.getDetails() != null && !income.getDetails().isEmpty()) {
                return addAssetIncomeDetails(income.getId(), income.getDetails());
            }
            
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 添加资产收益明细
    private static boolean addAssetIncomeDetails(int incomeId, List<AssetIncomeDetail> details) {
        String sql = """
            INSERT INTO asset_income_detail (income_id, date, amount, description)
            VALUES (?, ?, ?, ?)
            """;
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (AssetIncomeDetail detail : details) {
                pstmt.setInt(1, incomeId);
                pstmt.setString(2, detail.getDate().toString());
                pstmt.setDouble(3, detail.getAmount());
                pstmt.setString(4, detail.getDescription());
                pstmt.addBatch();
            }
            int[] results = pstmt.executeBatch();
            return Arrays.stream(results).allMatch(r -> r > 0);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 获取资产收益统计
    public static Map<String, Double> getAssetIncomeStats(int assetId) {
        Map<String, Double> stats = new HashMap<>();
        String sql = """
            SELECT 
                SUM(CASE WHEN date >= date('now', 'start of month') THEN amount ELSE 0 END) as monthly_income,
                SUM(CASE WHEN date >= date('now', 'start of year') THEN amount ELSE 0 END) as yearly_income,
                SUM(amount) as total_income
            FROM asset_income 
            WHERE asset_id = ?
            """;
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, assetId);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                stats.put("monthlyIncome", rs.getDouble("monthly_income"));
                stats.put("yearlyIncome", rs.getDouble("yearly_income"));
                stats.put("totalIncome", rs.getDouble("total_income"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stats;
    }

    // 获取资产收益历史
    public static List<AssetIncome> getAssetIncomeHistory(int assetId) {
        List<AssetIncome> incomes = new ArrayList<>();
        String sql = """
            SELECT * FROM asset_income 
            WHERE asset_id = ? 
            ORDER BY date DESC
            """;
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, assetId);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                AssetIncome income = new AssetIncome();
                income.setId(rs.getInt("id"));
                income.setAssetId(assetId);
                income.setDate(LocalDate.parse(rs.getString("date")));
                income.setType(rs.getString("type"));
                income.setAmount(rs.getDouble("amount"));
                income.setRemark(rs.getString("remark"));
                incomes.add(income);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return incomes;
    }

    // 获取资产收益明细
    public static List<AssetIncomeDetail> getAssetIncomeDetails(int assetId) {
        List<AssetIncomeDetail> details = new ArrayList<>();
        String sql = """
            SELECT d.* FROM asset_income_detail d
            JOIN asset_income i ON d.income_id = i.id
            WHERE i.asset_id = ?
            ORDER BY d.date
            """;
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, assetId);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                AssetIncomeDetail detail = new AssetIncomeDetail();
                detail.setId(rs.getInt("id"));
                detail.setIncomeId(rs.getInt("income_id"));
                detail.setDate(LocalDate.parse(rs.getString("date")));
                detail.setAmount(rs.getDouble("amount"));
                detail.setDescription(rs.getString("description"));
                details.add(detail);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return details;
    }

    // 获取资产趋势数据
    public static List<Map<String, Object>> getAssetTrendData(int userId, int months) {
        List<Map<String, Object>> trendData = new ArrayList<>();
        
        String sql = """
            WITH RECURSIVE dates(date) AS (
                SELECT date('now', 'start of month', '-' || ? || ' months')
                UNION ALL
                SELECT date(date, '+1 month')
                FROM dates
                WHERE date < date('now', 'start of month')
            )
            SELECT 
                strftime('%Y-%m', d.date) as month,
                COALESCE(SUM(CASE 
                    WHEN a.type_name IN ('花呗', '京东白条', '信用卡') THEN -a.balance 
                    ELSE a.balance 
                END), 0) as total
            FROM dates d
            LEFT JOIN assets a ON strftime('%Y-%m', d.date) = strftime('%Y-%m', a.create_time)
                AND a.user_id = ?
            GROUP BY strftime('%Y-%m', d.date)
            ORDER BY month DESC
            LIMIT ?
        """;
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, months - 1);
            pstmt.setInt(2, userId);
            pstmt.setInt(3, months);
            
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> data = new HashMap<>();
                data.put("month", rs.getString("month"));
                data.put("total", rs.getDouble("total"));
                trendData.add(data);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return trendData;
    }

    // 获取资产详情
    public static Asset getAssetById(int id) {
        String sql = "SELECT * FROM assets WHERE id = ?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                Asset asset = new Asset();
                asset.setId(rs.getInt("id"));
                asset.setUserId(rs.getInt("user_id"));
                asset.setName(rs.getString("name"));
                asset.setTypeName(rs.getString("type_name"));
                asset.setCategory(rs.getString("category"));
                asset.setAccountNumber(rs.getString("account_number"));
                asset.setBalance(rs.getDouble("balance"));
                asset.setRemark(rs.getString("remark"));
                return asset;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 辅助方法：根据资产类型获取类别
    private static String getAssetCategory(String type) {
        return switch (type) {
            case "储蓄卡", "信用卡" -> "银行账户";
            case "余额宝", "微信钱包", "支付宝" -> "网络支付";
            case "花呗", "京东白条" -> "信用账户";
            case "现金" -> "现金";
            case "理财产品", "基金", "股票" -> "投资理财";
            case "定期存款" -> "定期理财";
            case "公积金", "养老保险", "医疗保险" -> "社保公积金";
            default -> "其他";
        };
    }

    public static boolean addTransaction(Transaction transaction) {
        String sql = """
            INSERT INTO transactions (
                date, type, category, amount, description, 
                user_id
            ) VALUES (?, ?, ?, ?, ?, ?)
        """;
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, transaction.getDate().toString());
            pstmt.setString(2, transaction.getType());
            pstmt.setString(3, transaction.getCategory());
            pstmt.setDouble(4, transaction.getAmount());
            pstmt.setString(5, transaction.getDescription());
            pstmt.setInt(6, transaction.getUserId());
            
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static void refreshTransactions(ObservableList<Transaction> transactions, int userId, String type) {
        String sql = """
            SELECT t.*, u.name as user_name 
            FROM transactions t
            LEFT JOIN users u ON t.user_id = u.id
            WHERE t.user_id = ? AND t.type = ?
            ORDER BY t.date DESC
        """;
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, userId);
            pstmt.setString(2, type);
            ResultSet rs = pstmt.executeQuery();
            
            transactions.clear();
            while (rs.next()) {
                Transaction transaction = new Transaction();
                transaction.setId(rs.getInt("id"));
                transaction.setDate(LocalDate.parse(rs.getString("date")));
                transaction.setType(rs.getString("type"));
                transaction.setCategory(rs.getString("category"));
                transaction.setAmount(rs.getDouble("amount"));
                transaction.setDescription(rs.getString("description"));
                transaction.setUserId(rs.getInt("user_id"));
                transaction.setAssetName(rs.getString("user_name"));
                transactions.add(transaction);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 获取所有用户的资产
    public static List<Asset> getAllAssets() {
        List<Asset> assets = new ArrayList<>();
        String sql = """
            SELECT a.*, u.name as user_name 
            FROM assets a
            LEFT JOIN users u ON a.user_id = u.id
            ORDER BY a.category, a.name
        """;
        
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            
            ResultSet rs = stmt.executeQuery(sql);
            
            while (rs.next()) {
                Asset asset = new Asset();
                asset.setId(rs.getInt("id"));
                asset.setUserId(rs.getInt("user_id"));
                asset.setName(rs.getString("name"));
                asset.setTypeName(rs.getString("type_name"));
                asset.setCategory(rs.getString("category"));
                asset.setAccountNumber(rs.getString("account_number"));
                asset.setBalance(rs.getDouble("balance"));
                asset.setRemark(rs.getString("remark"));
                asset.setUserName(rs.getString("user_name")); // 添加用户名
                assets.add(asset);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return assets;
    }

    // 获取所有用户的资产趋势数据
    public static List<Map<String, Object>> getAllAssetTrendData(int months) {
        List<Map<String, Object>> trendData = new ArrayList<>();
        String sql = """
            WITH RECURSIVE dates(date) AS (
                SELECT date('now', 'start of month', '-' || ? || ' months')
                UNION ALL
                SELECT date(date, '+1 month')
                FROM dates
                WHERE date < date('now', 'start of month')
            )
            SELECT strftime('%Y-%m', dates.date) as month,
                   COALESCE(SUM(a.balance), 0) as total
            FROM dates
            LEFT JOIN assets a ON strftime('%Y-%m', a.create_time) <= strftime('%Y-%m', dates.date)
            GROUP BY strftime('%Y-%m', dates.date)
            ORDER BY month
        """;
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, months - 1);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Map<String, Object> data = new HashMap<>();
                data.put("month", rs.getString("month"));
                data.put("total", rs.getDouble("total"));
                trendData.add(data);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return trendData;
    }

    // 创建快照相关的表
    private static void createSnapshotTables(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        
        // 快照主表
        stmt.execute("""
            CREATE TABLE IF NOT EXISTS asset_snapshots (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                create_time DATETIME NOT NULL,
                description TEXT,
                total_assets REAL NOT NULL
            )
        """);
        
        // 快照详情表
        stmt.execute("""
            CREATE TABLE IF NOT EXISTS asset_snapshot_details (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                snapshot_id INTEGER NOT NULL,
                user_name TEXT NOT NULL,
                category TEXT NOT NULL,
                type_name TEXT NOT NULL,
                name TEXT NOT NULL,
                account_number TEXT,
                balance REAL NOT NULL,
                FOREIGN KEY (snapshot_id) REFERENCES asset_snapshots(id)
            )
        """);
        
        // 快照类别统计表
        stmt.execute("""
            CREATE TABLE IF NOT EXISTS asset_snapshot_category_totals (
                snapshot_id INTEGER NOT NULL,
                category TEXT NOT NULL,
                total REAL NOT NULL,
                PRIMARY KEY (snapshot_id, category),
                FOREIGN KEY (snapshot_id) REFERENCES asset_snapshots(id)
            )
        """);
    }

    // 创建资产快照
    public static boolean createAssetSnapshot(String description) {
        try (Connection conn = getConnection()) {
            conn.setAutoCommit(false);
            
            // 1. 获取所有资产数据
            List<Asset> allAssets = getAllAssets();
            double totalAssets = allAssets.stream()
                .mapToDouble(Asset::getBalance)
                .sum();
                
            // 2. 插入快照主表
            String sql = "INSERT INTO asset_snapshots (create_time, description, total_assets) VALUES (?, ?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, LocalDateTime.now().toString());
            pstmt.setString(2, description);
            pstmt.setDouble(3, totalAssets);
            pstmt.executeUpdate();
            
            // 获取生成的快照ID
            ResultSet rs = pstmt.getGeneratedKeys();
            int snapshotId = rs.getInt(1);
            
            // 3. 插入快照详情
            sql = """
                INSERT INTO asset_snapshot_details 
                (snapshot_id, user_name, category, type_name, name, account_number, balance)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            """;
            pstmt = conn.prepareStatement(sql);
            
            for (Asset asset : allAssets) {
                pstmt.setInt(1, snapshotId);
                pstmt.setString(2, getUsernameById(asset.getUserId()));
                pstmt.setString(3, asset.getCategory());
                pstmt.setString(4, asset.getTypeName());
                pstmt.setString(5, asset.getName());
                pstmt.setString(6, asset.getAccountNumber());
                pstmt.setDouble(7, asset.getBalance());
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            
            // 4. 插入类别统计
            Map<String, Double> categoryTotals = allAssets.stream()
                .collect(Collectors.groupingBy(
                    Asset::getCategory,
                    Collectors.summingDouble(Asset::getBalance)
                ));
                
            sql = "INSERT INTO asset_snapshot_category_totals (snapshot_id, category, total) VALUES (?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            
            for (Map.Entry<String, Double> entry : categoryTotals.entrySet()) {
                pstmt.setInt(1, snapshotId);
                pstmt.setString(2, entry.getKey());
                pstmt.setDouble(3, entry.getValue());
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            
            conn.commit();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 获取所有快照
    public static List<AssetSnapshot> getAllSnapshots() {
        List<AssetSnapshot> snapshots = new ArrayList<>();
        String sql = "SELECT * FROM asset_snapshots ORDER BY create_time DESC";
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                AssetSnapshot snapshot = new AssetSnapshot();
                snapshot.setId(rs.getInt("id"));
                snapshot.setCreateTime(LocalDateTime.parse(rs.getString("create_time")));
                snapshot.setDescription(rs.getString("description"));
                snapshot.setTotalAssets(rs.getDouble("total_assets"));
                
                // 获取快照详情
                snapshot.setDetails(getSnapshotDetails(snapshot.getId()));
                // 获取类别统计
                snapshot.setCategoryTotals(getSnapshotCategoryTotals(snapshot.getId()));
                
                snapshots.add(snapshot);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return snapshots;
    }

    // 获取快照详情
    private static List<AssetSnapshotDetail> getSnapshotDetails(int snapshotId) {
        List<AssetSnapshotDetail> details = new ArrayList<>();
        String sql = "SELECT * FROM asset_snapshot_details WHERE snapshot_id = ?";
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, snapshotId);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                AssetSnapshotDetail detail = new AssetSnapshotDetail();
                detail.setId(rs.getInt("id"));
                detail.setSnapshotId(snapshotId);
                detail.setUserName(rs.getString("user_name"));
                detail.setCategory(rs.getString("category"));
                detail.setTypeName(rs.getString("type_name"));
                detail.setName(rs.getString("name"));
                detail.setAccountNumber(rs.getString("account_number"));
                detail.setBalance(rs.getDouble("balance"));
                details.add(detail);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return details;
    }

    // 获取快照类别统计
    private static List<CategoryTotal> getSnapshotCategoryTotals(int snapshotId) {
        List<CategoryTotal> totals = new ArrayList<>();
        String sql = "SELECT * FROM asset_snapshot_category_totals WHERE snapshot_id = ?";
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, snapshotId);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                totals.add(new CategoryTotal(
                    rs.getString("category"),
                    rs.getDouble("total")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return totals;
    }

    // 根据用户ID获取用户名
    public static String getUsernameById(int userId) {
        String sql = "SELECT username FROM users WHERE id = ?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, userId);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                return rs.getString("username");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
} 